# coding:utf-8
"""
Created on 2016-11-16

@author: xiaochun
"""
import numpy as np
import pandas as pd
from UPQuant.RunQuant.const import DATABASES
#from settings import DATABASES
from sqlalchemy import create_engine
import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative
from sqlalchemy import PrimaryKeyConstraint
import datetime
import calendar

# 首先需要生成一个BaseModel类，作为所有模型类的基类
BaseModel = sqlalchemy.ext.declarative.declarative_base()


# 构建数据模型 FIN_IDX_ANA
class FIN_IDX_ANA(BaseModel):
    __tablename__ = 'fin_idx_ana'  # 表名
    __table_args__ = (
        PrimaryKeyConstraint('COM_UNI_CODE', 'END_DATE'),
    )

    # 表结构
    com_unicode = sqlalchemy.Column("COM_UNI_CODE", sqlalchemy.Integer)
    gpcode = sqlalchemy.Column("STK_CODE", sqlalchemy.String(10), nullable=False)
    date = sqlalchemy.Column("END_DATE", sqlalchemy.Date)
    BEPS = sqlalchemy.Column("BEPS", sqlalchemy.Float, default=0)
    DEPS = sqlalchemy.Column("DEPS", sqlalchemy.Float, default=0)
    EPSED = sqlalchemy.Column("EPSED", sqlalchemy.Float, default=0)
    EPSNED = sqlalchemy.Column("EPSNED", sqlalchemy.Float, default=0)
    BEPS_DED = sqlalchemy.Column("BEPS_DED", sqlalchemy.Float, default=0)
    DEPS_DED = sqlalchemy.Column("DEPS_DED", sqlalchemy.Float, default=0)
    EPSED_DED = sqlalchemy.Column("EPSED_DED", sqlalchemy.Float, default=0)
    BPS = sqlalchemy.Column("BPS", sqlalchemy.Float, default=0)
    BPSNED = sqlalchemy.Column("BPSNED", sqlalchemy.Float, default=0)
    PS_OCF = sqlalchemy.Column("PS_OCF", sqlalchemy.Float, default=0)
    PS_TOR = sqlalchemy.Column("PS_TOR", sqlalchemy.Float, default=0)
    PS_OR = sqlalchemy.Column("PS_OR", sqlalchemy.Float, default=0)
    PS_CR = sqlalchemy.Column("PS_CR", sqlalchemy.Float, default=0)
    PS_SR = sqlalchemy.Column("PS_SR", sqlalchemy.Float, default=0)
    PS_UP = sqlalchemy.Column("PS_UP", sqlalchemy.Float, default=0)
    PS_RE = sqlalchemy.Column("PS_RE", sqlalchemy.Float, default=0)
    PS_CN = sqlalchemy.Column("PS_CN", sqlalchemy.Float, default=0)

    PS_EBIT = sqlalchemy.Column("PS_EBIT", sqlalchemy.Float, default=0)
    PS_DIV = sqlalchemy.Column("PS_DIV", sqlalchemy.Float, default=0)
    ROEA = sqlalchemy.Column("ROEA", sqlalchemy.Float, default=0)
    ROEW = sqlalchemy.Column("ROEW", sqlalchemy.Float, default=0)
    ROED = sqlalchemy.Column("ROED", sqlalchemy.Float, default=0)
    ROEA_DED = sqlalchemy.Column("ROEA_DED", sqlalchemy.Float, default=0)
    ROEW_DED = sqlalchemy.Column("ROEW_DED", sqlalchemy.Float, default=0)
    ROED_DED = sqlalchemy.Column("ROED_DED", sqlalchemy.Float, default=0)
    ROE_AIC = sqlalchemy.Column("ROE_AIC", sqlalchemy.Float, default=0)
    ROA = sqlalchemy.Column("ROA", sqlalchemy.Float, default=0)
    ROA_NP = sqlalchemy.Column("ROA_NP", sqlalchemy.Float, default=0)
    ROIC = sqlalchemy.Column("ROIC", sqlalchemy.Float, default=0)
    ROE_Y = sqlalchemy.Column("ROE_YEAR", sqlalchemy.Float, default=0)
    ROA_Y = sqlalchemy.Column("ROA_YEAR", sqlalchemy.Float, default=0)
    ROA_NY = sqlalchemy.Column("ROA_NYEAR", sqlalchemy.Float, default=0)
    net_profit_margin = sqlalchemy.Column("SAL_NPR", sqlalchemy.Float, default=0)
    gross_profit_ratio = sqlalchemy.Column("SAL_GIR", sqlalchemy.Float, default=0)
    sal_cost_ratio = sqlalchemy.Column("SAL_COST", sqlalchemy.Float, default=0)
    sal_period_cost_ratio = sqlalchemy.Column("SAL_PFR", sqlalchemy.Float, default=0)

    TR_NP = sqlalchemy.Column("TR_NP", sqlalchemy.Float, default=0)
    TR_OP = sqlalchemy.Column("TR_OP", sqlalchemy.Float, default=0)
    TR_EBIT = sqlalchemy.Column("TR_EBIT", sqlalchemy.Float, default=0)
    TR_TC = sqlalchemy.Column("TR_TC", sqlalchemy.Float, default=0)
    TR_OF = sqlalchemy.Column("TR_OF", sqlalchemy.Float, default=0)
    TR_MF = sqlalchemy.Column("TR_MF", sqlalchemy.Float, default=0)
    TR_FF = sqlalchemy.Column("TR_FF", sqlalchemy.Float, default=0)
    TR_AL = sqlalchemy.Column("TR_AL", sqlalchemy.Float, default=0)
    TP_ONI = sqlalchemy.Column("TP_ONI", sqlalchemy.Float, default=0)
    TP_VCI = sqlalchemy.Column("TP_VCI", sqlalchemy.Float, default=0)
    TP_NON = sqlalchemy.Column("TP_NON", sqlalchemy.Float, default=0)
    TP_TAX = sqlalchemy.Column("TP_TAX", sqlalchemy.Float, default=0)
    TP_DNP = sqlalchemy.Column("TP_DNP", sqlalchemy.Float, default=0)
    OR_SAL = sqlalchemy.Column("OR_SAL", sqlalchemy.Float, default=0)
    OR_OCF = sqlalchemy.Column("OR_OCF", sqlalchemy.Float, default=0)
    OR_ONI = sqlalchemy.Column("OR_ONI", sqlalchemy.Float, default=0)
    ASS_DEBT = sqlalchemy.Column("ASS_DEBT", sqlalchemy.Float, default=0)

    equity_mult = sqlalchemy.Column("EM", sqlalchemy.Float, default=0)
    TA_CA = sqlalchemy.Column("TA_CA", sqlalchemy.Float, default=0)
    TA_NCA = sqlalchemy.Column("TA_NCA", sqlalchemy.Float, default=0)
    TA_TA = sqlalchemy.Column("TA_TA", sqlalchemy.Float, default=0)
    TC_PCE = sqlalchemy.Column("TC_PCE", sqlalchemy.Float, default=0)
    TC_IBD = sqlalchemy.Column("TC_IBD", sqlalchemy.Float, default=0)
    TL_CL = sqlalchemy.Column("TL_CL", sqlalchemy.Float, default=0)
    TL_NCL = sqlalchemy.Column("TL_NCL", sqlalchemy.Float, default=0)
    cur_ratio = sqlalchemy.Column("CR", sqlalchemy.Float, default=0)
    quick_ratio = sqlalchemy.Column("QR", sqlalchemy.Float, default=0)
    keep_quick_ratio = sqlalchemy.Column("KQR", sqlalchemy.Float, default=0)
    equity_ratio = sqlalchemy.Column("ER", sqlalchemy.Float, default=0)
    TL_PCE = sqlalchemy.Column("TL_PCE", sqlalchemy.Float, default=0)
    IBD_PCE = sqlalchemy.Column("IBD_PCE", sqlalchemy.Float, default=0)
    TL_TA = sqlalchemy.Column("TL_TA", sqlalchemy.Float, default=0)
    IBD_TA = sqlalchemy.Column("IBD_TA", sqlalchemy.Float, default=0)
    ND_TA = sqlalchemy.Column("ND_TA", sqlalchemy.Float, default=0)
    TL_OCF = sqlalchemy.Column("TL_OCF", sqlalchemy.Float, default=0)
    IBD_OCF = sqlalchemy.Column("IBD_OCF", sqlalchemy.Float, default=0)

    CL_OCF = sqlalchemy.Column("CL_OCF", sqlalchemy.Float, default=0)
    ND_OCF = sqlalchemy.Column("ND_OCF", sqlalchemy.Float, default=0)
    IF_EBIT = sqlalchemy.Column("IF_EBIT", sqlalchemy.Float, default=0)
    WK_LD = sqlalchemy.Column("WK_LD", sqlalchemy.Float, default=0)
    op_circle = sqlalchemy.Column("OPE_CYC", sqlalchemy.Integer, default=0)
    inventory_TOD = sqlalchemy.Column("INV_DAYS", sqlalchemy.Integer, default=0)
    receivables_TOD = sqlalchemy.Column("ARC_DAYS", sqlalchemy.Integer, default=0)
    inventory_TOR = sqlalchemy.Column("INV_RATE", sqlalchemy.Float, default=0)
    receivables_TOR = sqlalchemy.Column("ARC_RATE", sqlalchemy.Float, default=0)
    cur_assets_TOR = sqlalchemy.Column("CA_RATE", sqlalchemy.Float, default=0)
    fixed_assets_TOR = sqlalchemy.Column("FA_RATE", sqlalchemy.Float, default=0)
    tot_assets_TOR = sqlalchemy.Column("TA_RATE", sqlalchemy.Float, default=0)
    payable_TOR = sqlalchemy.Column("AP_RATE", sqlalchemy.Float, default=0)
    payable_TOD = sqlalchemy.Column("AP_DAYS", sqlalchemy.Integer, default=0)
    BEPS_YOY = sqlalchemy.Column("BEPS_YOY", sqlalchemy.Float, default=0)
    DEPS_YOY = sqlalchemy.Column("DEPS_YOY", sqlalchemy.Float, default=0)
    PS_OCF_YOY = sqlalchemy.Column("PS_OCF_YOY", sqlalchemy.Float, default=0)
    tot_op_revenue_YOY = sqlalchemy.Column("TR_YOY", sqlalchemy.Float, default=0)
    op_revenue_YOY = sqlalchemy.Column("OR_YOY", sqlalchemy.Float, default=0)

    op_profit_YOY = sqlalchemy.Column("OP_YOY", sqlalchemy.Float, default=0)
    tot_profit_YOY = sqlalchemy.Column("TP_YOY", sqlalchemy.Float, default=0)
    PCNP_YOY = sqlalchemy.Column("PCNP_YOY", sqlalchemy.Float, default=0)
    PCNDP_YOY = sqlalchemy.Column("PCNDP_YOY", sqlalchemy.Float, default=0)
    OCF_YOY = sqlalchemy.Column("OCF_YOY", sqlalchemy.Float, default=0)
    ROE_YOY = sqlalchemy.Column("ROE_YOY", sqlalchemy.Float, default=0)
    BPS_YTD = sqlalchemy.Column("BPS_YTD", sqlalchemy.Float, default=0)
    tot_assets_YTD = sqlalchemy.Column("TA_YTD", sqlalchemy.Float, default=0)
    PCE_YTD = sqlalchemy.Column("PCE_YTD", sqlalchemy.Float, default=0)
    non_recur_gains_and_losses = sqlalchemy.Column("INC_A", sqlalchemy.Float, default=0)
    NP_DED = sqlalchemy.Column("INC_B", sqlalchemy.Float, default=0)
    gross_profit = sqlalchemy.Column("INC_C", sqlalchemy.Float, default=0)
    op_act_np = sqlalchemy.Column("INC_D", sqlalchemy.Float, default=0)
    net_value_chg = sqlalchemy.Column("INC_E", sqlalchemy.Float, default=0)
    EBIT = sqlalchemy.Column("INC_F", sqlalchemy.Float, default=0)
    tot_op_revenue = sqlalchemy.Column("INC_H", sqlalchemy.Float, default=0)
    net_profit = sqlalchemy.Column("INC_I", sqlalchemy.Float, default=0)
    op_revenue = sqlalchemy.Column("INC_J", sqlalchemy.Float, default=0)

    tot_investment_capital = sqlalchemy.Column("BAL_A", sqlalchemy.Float, default=0)
    working_capital = sqlalchemy.Column("BAL_B", sqlalchemy.Float, default=0)
    net_working_capital = sqlalchemy.Column("BAL_C", sqlalchemy.Float, default=0)
    tangible_assets = sqlalchemy.Column("BAL_D", sqlalchemy.Float, default=0)
    retained_earnings = sqlalchemy.Column("BAL_E", sqlalchemy.Float, default=0)
    interest_bearing_debt = sqlalchemy.Column("BAL_F", sqlalchemy.Float, default=0)
    net_debt = sqlalchemy.Column("BAL_G", sqlalchemy.Float, default=0)
    non_interest_cur_liablities = sqlalchemy.Column("BAL_H", sqlalchemy.Float, default=0)
    non_interest_noncur_liablities = sqlalchemy.Column("BAL_I", sqlalchemy.Float, default=0)
    current_assets = sqlalchemy.Column("BAL_M", sqlalchemy.Float, default=0)
    current_liablities = sqlalchemy.Column("BAL_N", sqlalchemy.Float, default=0)
    tot_assets = sqlalchemy.Column("BAL_O", sqlalchemy.Float, default=0)
    tot_liablities = sqlalchemy.Column("BAL_P", sqlalchemy.Float, default=0)


# 构建数据模型 COM_SHARE_STRU
class COM_SHARE_STRU(BaseModel):
    __tablename__ = 'com_share_stru'  # 表名
    __table_args__ = (
        PrimaryKeyConstraint('COM_UNI_CODE', 'CHAN_DATE'),
    )
    # 表结构
    com_unicode = sqlalchemy.Column("COM_UNI_CODE", sqlalchemy.Integer)
    gpcode = sqlalchemy.Column("STK_CODE", sqlalchemy.String(10), nullable=False)
    date = sqlalchemy.Column("CHAN_DATE", sqlalchemy.Date)
    TOT_SHARE = sqlalchemy.Column("TOT_SHARE", sqlalchemy.Integer, default=0)
    FLOAT_SHARE = sqlalchemy.Column("FLOAT_SHARE", sqlalchemy.Integer, default=0)
    A_FLOAT_SHARE = sqlalchemy.Column("A_FLOAT_SHARE", sqlalchemy.Integer, default=0)
    LIST_FLOAT_ASHR = sqlalchemy.Column("LIST_FLOAT_ASHR", sqlalchemy.Integer, default=0)
    LIST_FLOAT_MAN = sqlalchemy.Column("LIST_FLOAT_MAN", sqlalchemy.Integer, default=0)
    B_SHARE = sqlalchemy.Column("B_SHARE", sqlalchemy.Integer, default=0)
    H_SHARE = sqlalchemy.Column("H_SHARE", sqlalchemy.Integer, default=0)
    S_SHARE = sqlalchemy.Column("S_SHARE", sqlalchemy.Integer, default=0)
    N_SHARE = sqlalchemy.Column("N_SHARE", sqlalchemy.Integer, default=0)
    OTH_ABRO_SHARE = sqlalchemy.Column("OTH_ABRO_SHARE", sqlalchemy.Integer, default=0)
    OTH_FLOAT_SHARE = sqlalchemy.Column("OTH_FLOAT_SHARE", sqlalchemy.Integer, default=0)
    TOT_REST = sqlalchemy.Column("TOT_REST", sqlalchemy.Integer, default=0)
    TOT_STATE_LEG_REST = sqlalchemy.Column("TOT_STATE_LEG_REST", sqlalchemy.Integer, default=0)
    STATE_REST = sqlalchemy.Column("STATE_REST", sqlalchemy.Integer, default=0)
    STATE_LEG_REST = sqlalchemy.Column("STATE_LEG_REST", sqlalchemy.Integer, default=0)
    
    DOM_LEG_REST = sqlalchemy.Column("DOM_LEG_REST", sqlalchemy.Integer, default=0)
    DOM_NATU_REST = sqlalchemy.Column("DOM_NATU_REST", sqlalchemy.Integer, default=0)
    FORE_REST = sqlalchemy.Column("FORE_REST", sqlalchemy.Integer, default=0)
    FORE_LEG_RES = sqlalchemy.Column("FORE_LEG_RES", sqlalchemy.Integer, default=0)
    FORE_NATU_RES = sqlalchemy.Column("FORE_NATU_RES", sqlalchemy.Integer, default=0)
    OTH_SPON_RES = sqlalchemy.Column("OTH_SPON_RES", sqlalchemy.Integer, default=0)
    RAIS_LEG_RES = sqlalchemy.Column("RAIS_LEG_RES", sqlalchemy.Integer, default=0)
    INNER_STAFF_RES = sqlalchemy.Column("INNER_STAFF_RES", sqlalchemy.Integer, default=0)
    MAN_RES = sqlalchemy.Column("MAN_RES", sqlalchemy.Integer, default=0)
    PREF_RES = sqlalchemy.Column("PREF_RES", sqlalchemy.Integer, default=0)
    TRANS_RES = sqlalchemy.Column("TRANS_RES", sqlalchemy.Integer, default=0)
    OTH_RES = sqlalchemy.Column("OTH_RES", sqlalchemy.Integer, default=0)
    FUND_PLACE_RES = sqlalchemy.Column("FUND_PLACE_RES", sqlalchemy.Integer, default=0)
    LEG_PLACE_RES = sqlalchemy.Column("LEG_PLACE_RES", sqlalchemy.Integer, default=0)
    TOT_NFLOAT = sqlalchemy.Column("TOT_NFLOAT", sqlalchemy.Integer, default=0)
    TOT_STATE_LEG_NFLOAT = sqlalchemy.Column("TOT_STATE_LEG_NFLOAT", sqlalchemy.Integer, default=0)
    STATE_NFLOAT = sqlalchemy.Column("STATE_NFLOAT", sqlalchemy.Integer, default=0)
    STATE_LEG_NFLOAT = sqlalchemy.Column("STATE_LEG_NFLOAT", sqlalchemy.Integer, default=0)
    DOM_LEG_NFLOAT = sqlalchemy.Column("DOM_LEG_NFLOAT", sqlalchemy.Integer, default=0)
    
    DOM_NATU_NFLOAT = sqlalchemy.Column("DOM_NATU_NFLOAT", sqlalchemy.Integer, default=0)
    FORE_NFLOAT = sqlalchemy.Column("FORE_NFLOAT", sqlalchemy.Integer, default=0)
    FORE_LEG_NFLOAT = sqlalchemy.Column("FORE_LEG_NFLOAT", sqlalchemy.Integer, default=0)
    FORE_NATU_NFLOAT = sqlalchemy.Column("FORE_NATU_NFLOAT", sqlalchemy.Integer, default=0)
    OTH_SPON_NFLOAT = sqlalchemy.Column("OTH_SPON_NFLOAT", sqlalchemy.Integer, default=0)
    RAIS_LEG_NFLOAT = sqlalchemy.Column("RAIS_LEG_NFLOAT", sqlalchemy.Integer, default=0)
    INNER_STAFF_NFLOAT = sqlalchemy.Column("INNER_STAFF_NFLOAT", sqlalchemy.Integer, default=0)
    MAN_NFLOAT = sqlalchemy.Column("MAN_NFLOAT", sqlalchemy.Integer, default=0)
    PREF_NFLOAT = sqlalchemy.Column("PREF_NFLOAT", sqlalchemy.Integer, default=0)
    TRANS_NFLOAT = sqlalchemy.Column("TRANS_NFLOAT", sqlalchemy.Integer, default=0)
    OTH_NFLOAT = sqlalchemy.Column("OTH_NFLOAT", sqlalchemy.Integer, default=0)
    A_SHARE = sqlalchemy.Column("A_SHARE", sqlalchemy.Integer, default=0)
    MAIN_HOLDER_SHR_PROP = sqlalchemy.Column("MAIN_HOLDER_SHR_PROP", sqlalchemy.Float, default=0)
    CIR_HOLDER_SHR_PROP = sqlalchemy.Column("CIR_HOLDER_SHR_PROP", sqlalchemy.Float, default=0)
    

class DataCenterFinData(object):
    def __init__(self):
        self._engine_mysql = create_engine(
            DATABASES["hqdata"].get("engine") + "://" + DATABASES["hqdata"].get("user") + ":" + DATABASES[
                "hqdata"].get("password") + "@" + DATABASES["hqdata"].get("host") + "/" + DATABASES["hqdata"].get(
                "db") + "?charset=utf8")
        

    def query(self, *entities, **kwargs):
        # 利用Session对象链接数据库
        DBSession = sqlalchemy.orm.sessionmaker(bind=self._engine_mysql)  # 创建回话类
        session = DBSession()  # 创建回话对象
        try:
            q = session.query(*entities, **kwargs)
            for columnInfo in q.column_descriptions:
                entity = columnInfo["entity"] # 获取query查询的 映射类
                return session.query(*entities, **kwargs).add_columns(entity.date, entity.gpcode)   #默认查出日期、股票代码两列
            
        except Exception as excep:
            session.rollback()
            raise

        session.close()

    def get_fundamentals(self, query_Object, entry_date=None, interval=None):
        if entry_date is None:
            entry_date = datetime.datetime.now()
            entry_date = entry_date.strftime("%Y-%m-%d")

        entry_date = datetime.datetime.strptime(entry_date, "%Y-%m-%d")
        nEntryDate = entry_date.year * 10000 + entry_date.month * 100 + entry_date.day
        dict_stopdate = {}
        nStopDate = None

        if interval is not None:
            if len(interval) >= 2:
                interval_num = int(interval[0:-1])
                interval_q = interval[-1]

                if interval_q == 'y':  # 年
                    for i in range(1, interval_num):
                        stop_year = entry_date.year - i
                        stop_mon = entry_date.month
                        stop_day = min(entry_date.day, calendar.monthrange(stop_year, stop_mon)[1])  # 天 不能超过当月 最大天数值
                        stop_date = datetime.datetime(stop_year, stop_mon, stop_day)
                        nStopDate = stop_date.year * 10000 + stop_date.month * 100 + stop_date.day
                        dict_stopdate[nStopDate] = stop_date
                if interval_q == 'm':  # 月
                    for i in range(1, interval_num):
                        month = entry_date.month - 1 - i
                        stop_year = entry_date.year + month / 12
                        stop_mon = month % 12 + 1
                        stop_day = min(entry_date.day, calendar.monthrange(stop_year, stop_mon)[1])
                        stop_date = datetime.datetime(stop_year, stop_mon, stop_day)
                        nStopDate = stop_date.year * 10000 + stop_date.month * 100 + stop_date.day
                        dict_stopdate[nStopDate] = stop_date
                if interval_q == 'd':  # 日
                    for i in range(1, interval_num):
                        stop_date = entry_date - datetime.timedelta(days=i)
                        nStopDate = stop_date.year * 10000 + stop_date.month * 100 + stop_date.day
                        dict_stopdate[nStopDate] = stop_date
                if interval_q == 'w':  # 周
                    for i in range(1, interval_num):
                        stop_date = entry_date - datetime.timedelta(days=i * 7)
                        nStopDate = stop_date.year * 10000 + stop_date.month * 100 + stop_date.day
                        dict_stopdate[nStopDate] = stop_date
                if interval_q == 'q':  # 季
                    for i in range(1, interval_num):
                        month = entry_date.month - 1 - i * 3
                        stop_year = entry_date.year + month / 12
                        stop_mon = month % 12 + 1
                        stop_day = min(entry_date.day, calendar.monthrange(stop_year, stop_mon)[1])
                        stop_date = datetime.datetime(stop_year, stop_mon, stop_day)
                        nStopDate = stop_date.year * 10000 + stop_date.month * 100 + stop_date.day
                        dict_stopdate[nStopDate] = stop_date

        columns = []
        for columnInfo in query_Object.column_descriptions:
            columns.append(columnInfo["name"])  # 获取query查询的 映射类的列名

        DBSession = sqlalchemy.orm.sessionmaker(bind=self._engine_mysql)  # 创建回话类
        session = DBSession()  # 创建回话对象
        dfQuery = pd.read_sql(query_Object.statement, session.bind)  # 返回query从 数据库表 中查询的DataFrame
        dfQuery.columns = columns
        List_gpcode = list(set(list(dfQuery['gpcode']))) #获取查询的股票列表
        
        df_return = None
        df_entrydate = df_stopdate = None
        for gpcode in List_gpcode:
            qstr = "gpcode =='{0}'".format(gpcode)
            dfSort = dfQuery.query(qstr)  # 获取当前股票的数据
            dfSort = dfSort.sort_values('date', ascending=0)  # 排序
            for datenow in dfSort["date"]:
                datenow = datenow.year * 10000 + datenow.month * 100 + datenow.day
                if datenow <= nEntryDate:
                    qstr = "date =={0}".format(datenow)
                    df_entrydate = dfSort.query(qstr)  # 获取开始日期的数据
                    if not df_entrydate["date"].empty:
                        df_entrydate.loc[df_entrydate.index, "date"] = entry_date  # 将数据库里的季报日期 替换成传进来的开始日期
                        if  df_return is None:
                            df_return = df_entrydate
                        else:
                            df_return = df_return.append(df_entrydate)
                        break
                    
        for gpcode in List_gpcode:
            qstr = "gpcode =='{0}'".format(gpcode)
            dfSort = dfQuery.query(qstr)  # 获取当前股票的数据
            dfSort = dfSort.sort_values('date', ascending=0)  # 排序
            for key in dict_stopdate:  # 截止日期可能有多个
                for datenow in dfSort["date"]:
                    datenow = datenow.year * 10000 + datenow.month * 100 + datenow.day
                    if datenow <= key:
                        qstr = "date =={0}".format(datenow)
                        df_stopdate = dfSort.query(qstr)  # 获取截止日期的数据
                        if not df_stopdate["date"].empty:
                            df_stopdate.loc[df_stopdate.index, "date"] = dict_stopdate[key]  # 将数据库里的季报日期 替换成计算出的截止日期
                            df_return = df_return.append(df_stopdate)  # 合并
                            break

        df_return = df_return.sort_values(['date', 'gpcode'], ascending=[0,1])  # 排序
        df_return.columns = columns  # 将 df_return 的数据库表的列名 替换成映射类的列名
        df_return = df_return.set_index([columns[len(columns) - 2], columns[len(columns) - 1]])  # 将 [日期,股票代码] 这两列组合设置成索引
        return df_return


if __name__ == '__main__':
    pass
#     findata = DataCenterFinData()
#     q = findata.query(FIN_IDX_ANA.ASS_DEBT).filter(FIN_IDX_ANA.gpcode.in_(['000001.XSHE','600000.XSHG']))
#     findata.get_fundamentals(q)
#     
#     q = findata.query(COM_SHARE_STRU.TOT_SHARE).filter(COM_SHARE_STRU.gpcode.in_(['000001.XSHE','600000.XSHG']))
#     findata.get_fundamentals(q)
